//
// Created by mi on 2024/2/3.
//
//https://dev.mysql.com/doc/dev/connector-cpp/latest/
//官网中说到 c++ 的library有三种api，X DevAPI 、 X DevAPI for C 、legacy JDBC4-based API
// 第三种已经过时
//docker run  -e MYSQL_ROOT_PASSWORD=123456  -p 3306:3306 -p 33060:33060 --name mysql -v /home/app/mysql/conf/my.cnf:/etc/my.cnf -v /home/app/mysql/data:/var/lib/mysql -d mysql
//33060 是 x plugin 的端口。
//collection有三个字段 doc,id,_json_schema. doc是json数据。有点类似mongodb.
//schema 理解为数据库.collection理解为跟table平级的东西
//这个库，操作数据库会抛异常。。比如sql语法错误，所以记得要捕获异常。
//savepoints :事务在执行过程中，设置savepoints，如果接下来事务出错，可以回滚到savepoints

//CREATE TABLE `t_student_table` (
//`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
//`student_code` varchar(32) NOT NULL DEFAULT '' COMMENT '学号',
//`name` varchar(32) NOT NULL DEFAULT '' COMMENT '名字',
//`address` varchar(32) NOT NULL DEFAULT '' COMMENT '地址',
//`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
//`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
//`delete_flag` bit(1) NOT NULL DEFAULT b'0' COMMENT '删除状态（1-删除 0-未删除）',
//`disable_flag` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否禁用（1-是 0-否）',
//PRIMARY KEY (`id`)
//) ENGINE=InnoDB COMMENT='t_student_table';

#include <iostream>
#include <mysqlx/xdevapi.h>

using ::std::cout;
using ::std::endl;
using namespace mysqlx;

int main() {
    //std::shared_ptr<Session> session = nullptr;
    try {
        Client cli(SessionOption::HOST, "192.168.0.250",
                   SessionOption::PORT, 33060,
                   SessionOption::USER, "root",
                   SessionOption::DB, "yezongwen",
                   SessionOption::PWD, "123456",
                   ClientOption::POOL_MAX_IDLE_TIME, 0, //关闭空闲句柄的等待时间，0：一直等待
                   ClientOption::POOL_QUEUE_TIMEOUT, 0, //取句柄，等待时间。0：一直等待
                   ClientOption::POOL_MAX_SIZE, 7);
//        session = std::make_shared<Session>(SessionOption::HOST, "192.168.0.250",
//                                            SessionOption::PORT, 33060,
//                                            SessionOption::USER, "root",
//                                            SessionOption::DB, "yezongwen",
//                                            SessionOption::PWD, "123456");
        //作用域结束，也会调用session.close()方法

        Session session = cli.getSession();
        // Database objects that belong to some schema
        Schema db = session.getSchema("yezongwen");
        //增删改查
        //在mysql中，schema可以理解是数据库
        Table table = db.getTable("t_student_table");
        table.remove().where("1=1").execute();
        table.insert("student_code", "name", "address").values("000002", "zhangshan", "guangxi")
                .values("0000003", "lisi", "zhongguo").values("000004", "wangwu", "shanghai").execute();

        //where,order by ,having ,limit ,offset,group by ,共享锁，独占锁 都支持，貌似desc和esc不支持
        //https://dev.mysql.com/doc/x-devapi-userguide/en/sql-crud-functions.html 这里有对方法调用顺序的说明
        //https://dev.mysql.com/doc/x-devapi-userguide/en/crud-ebnf-table-crud-functions.html
        RowResult result = table.select("name", "address").where("student_code = :param").orderBy("id").limit(1).offset(5).bind(
                "param",
                "000002").execute();


        table.update().set("address", "hunan").where("student_code = :param").limit(1).bind("param",
                                                                                            "000002").execute();
        Row row = result.fetchOne();
        for (int i = 0; i < row.colCount(); i++) {
            std::cout << row[i] << std::endl;
        }
        Result removeResult = table.remove().where("student_code = :param").limit(1).bind("param",
                                                                                          "000002").execute();
        //事务
        // Start a transaction
        session.startTransaction();
        try {
            table.insert("student_code", "name", "address").values("000007", "zhangshan", "guangxi").execute();
            table.insert("student_code", "name", "address").values("000008", "zhangshan", "guangxi").execute();
            table.insert("student_code", "name", "address").values("000009", "zhangshan", "guangxi").execute();
            // Commit the transaction if everything went well
            session.commit();
        }
        catch (const Error &err) {
            // Rollback the transaction in case of an error
            session.rollback();
        }

        //预处理:官网文档没有看到太多的相关说明。//复杂的sql也可以通过 session来执行。
        {
            string sql = "select * from t_student_table where student_code = ? and name= ?";
            SqlStatement statement = session.sql(sql);
            statement.bind("000008", "zhangshan");
            auto res = statement.execute();
            Row row = res.fetchOne();
            if (!row.isNull()) {
                for (int i = 0; i < row.colCount(); i++) {
                    std::cout << row[i] << std::endl;
                }
            }
        }
        session.close();
        cli.close();
    }
    catch (std::exception &e) {
        std::cout << e.what() << std::endl;
    }
    return 0;
}
